<?php

 
 Zend_Loader::loadClass("BaseDBRows");
 Zend_Loader::loadClass("Receipt");
 class Receipts extends BaseDBRows
 {
 	protected function setUp()
 	{
 		$this->_tableName = Receipt::_tablename;
 	}	
	
	public function getReceipts($org_id,$param=array(),$withoutssp=0) {
		
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("purchaseOrder");
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("OrganizationContact");
		Zend_Loader::loadClass("Contact");
		Zend_Loader::loadClass("SupplierPerformance");
		
		$query = new QueryCreator();
		$query->addSelect(" po.po_no,org.name as org_name,contact.name as contact_name,rcpt.date_receipt");
		$query->addSelect(" sum(rcpt.received_amount) as received_amount,po.po_id,rcpt.supplier_org_id,ifnull(sp.ssp_id,0) as ssp_id,rcpt.receipt_id ");
		$query->addFrom($this->_tableName . " rcpt ");
		$query->addJoin(" LEFT JOIN ". Organization::_tablename ." org on org.org_id = rcpt.supplier_org_id");
		$query->addJoin(" LEFT JOIN ". purchaseOrderDetail::_tablename ." podetail on podetail.po_detail_id = rcpt.reference_id and rcpt.source = 'PO' ");
		$query->addJoin(" LEFT JOIN ". purchaseOrder::_tablename . " po on po.po_id=podetail.po_id ");
		$query->addJoin(" LEFT JOIN ". OrganizationContact::_tablename ." orgcontact on orgcontact.org_contact_id = org.primary_contact_id");
		$query->addJoin(" LEFT JOIN ". Contact::_tablename . " contact on contact.contact_id = orgcontact.contact_id ");
		$query->addJoin(" LEFT JOIN ". SupplierPerformance::_tablename . " sp on sp.reference_receipt_id = rcpt.receipt_id ");
		
		$query->addWhere(" rcpt.org_id = ". $org_id);
		
		if($withoutssp)
			$query->addWhere(" (sp.ssp_id is null or sp.ssp_id = 0 )");
		
		if($param['supplier']) 
			$query->addWhere(" rcpt.supplier_org_id =". $param['supplier'] );
			
		if($param['datefrom']) {
			if($param['dateto']) {
				$query->addWhere(" rcpt.date_receipt between '". $param['datefrom'] . "' and '". $param['dateto'] . "'" );
			} else {
				$query->addWhere(" rcpt.date_receipt >= '". $param['datefrom'] . "'");	
			}
		}
		
		if($param['dateto'] && strlen($param['datefrom']) == 0) {
			$query->addWhere(" rcpt.date_receipt <= '". $param['dateto'] . "'");	
		}
		
			
		$query->addGroupBy(" rcpt.supplier_org_id,po.po_no,rcpt.date_receipt");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
	public function getReceiptDetails($poid,$supplier_org_id,$date){
		
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("Organism");
		Zend_Loader::loadClass("purchaseOrderDetail");
		Zend_Loader::loadClass("OrganismAttribute");
		Zend_Loader::loadClass("Size");
		Zend_Loader::loadClass("ReceiptReject");
		Zend_Loader::loadClass("RejectReason");
		Zend_Loader::loadClass("OrganizationOrganism");
		
		$query = new QueryCreator();
		$query->addSelect("organism.moid,organism.name,size.size,rcpt.received_quantity,rcpt.doa_quantity,rcpt.reject_quantity,rcptrj.reject_quantity as rqty,reason.reason as reason_name,rcpt.receipt_id,rcptrj.reject_reason");
		$query->addFrom($this->_tableName . " rcpt ");
		$query->addJoin(" LEFT JOIN ". OrganizationOrganism::_tablename . " orgorg on orgorg.org_organism_id = rcpt.org_organism_id");
		$query->addJoin(" LEFT JOIN ". Organism::_tablename . " organism  on organism.organism_id =orgorg.organism_id " );
		$query->addJoin(" LEFT JOIN ". purchaseOrderDetail::_tablename . " podetail on podetail.po_detail_id = rcpt.reference_id ");
		//$query->addJoin(" LEFT JOIN ". OrganismAttribute::_tablename . " orgatt on orgatt.organism_id = podetail.organism_id and orgatt.size_code = podetail.size_code ");
		$query->addJoin(" LEFT JOIN ". Size::_tablename . " size on size.size_code = podetail.size_code ");
		$query->addJoin(" LEFT JOIN ". ReceiptReject::_tablename . " rcptrj on rcptrj.receipt_id = rcpt.receipt_id ");
		$query->addJoin(" LEFT JOIN ". RejectReason::_tablename . " reason on rcptrj.reject_reason=reason.reject_reason_id ");
		
		$query->addWhere(" rcpt.supplier_org_id = ". $supplier_org_id . " and date '". $date ."' and podetail.po_id = ". $poid);
		
		
		$db = DBCon::instance();
		$result = $db->executeQuery($query->createSQL());
		$data = array();
		foreach($result as $key => $value) {
			if(!isset($data[$value['receipt_id']]))
				$data[$value['receipt_id']] = $value;
				
			$data[$value['receipt_id']]['reject'][$value['reject_reason']]['reason'] = $value['reason_name'];
			$data[$value['receipt_id']]['reject'][$value['reject_reason']]['qty'] = $value['rqty'];
			
		}
		return $data;
		
	}

	public function getReceiptDOAByOrgId($orgId,$supplierId="",$tank="",$moid="",$dateFrom="",$dateTo="",$searchPO="",$groupBy) 
	{
		Zend_Loader::loadClass("purchaseOrder");
		Zend_Loader::loadClass("OrganizationOrganism");
		Zend_Loader::loadClass("Organism");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("Inventory");
		Zend_Loader::loadClass("Tank");
		Zend_Loader::loadClass("OrganizationAddress");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("Country");
		Zend_Loader::loadClass("QueryCreator");	
		
		$query = new QueryCreator();
		
		if($groupBy == 0 || $groupBy == "") {
			$query->addSelect("r.date_receipt, r.reference_id, ord.po_no, o.moid, o.name specie, r.supplier_org_id, org.name supplier_name, t.name tank_name, r.doa_quantity, r.received_quantity, c.country");	
		}
		elseif ($groupBy == 1) {
			$query->addSelect("min(r.date_receipt) first_receipt, max(r.date_receipt) last_receipt, r.supplier_org_id, c.country, org.name supplier_name, count(distinct ord.po_no) count_po_no, count(distinct o.moid) count_moid, count(distinct t.tank_id) count_tank, sum(doa_quantity) sum_doa_quantity, sum(r.received_quantity) sum_received_quantity");
			$query->addGroupBy("r.supplier_org_id, org.name, c.country");
		}
		elseif ($groupBy == 2) {
			$query->addSelect("min(r.date_receipt) first_receipt, max(r.date_receipt) last_receipt, o.moid, count(distinct r.supplier_org_id) count_supplier, count(distinct ord.po_no) count_po_no, count(distinct t.tank_id) count_tank, sum(doa_quantity) sum_doa_quantity, sum(r.received_quantity) sum_received_quantity");
			$query->addGroupBy("o.moid");
		}
		elseif ($groupBy == 3) {
			$query->addSelect("min(r.date_receipt) first_receipt, max(r.date_receipt) last_receipt, t.name tank_name, count(distinct r.supplier_org_id) count_supplier, count(distinct ord.po_no) count_po_no, count(distinct o.moid) count_moid, sum(doa_quantity) sum_doa_quantity, sum(r.received_quantity) sum_received_quantity");
			$query->addGroupBy("t.name");
		}
		
		$query->addFrom($this->_tableName . " r ");
		$query->addJoin(" left join " . OrganizationAddress::_tablename . " oa on r.supplier_org_address_id = oa.org_address_id ");
		$query->addJoin(" left join " . Address::_tablename . " a on oa.address_id = a.address_id ");
		$query->addJoin(" left join " . Country::_tablename . " c on a.country_code = c.country_code ");
		$query->addJoin("," . purchaseOrder::_tablename . " ord ");
		$query->addJoin("," . OrganizationOrganism::_tablename . " oo ");
		$query->addJoin("," . Organism::_tablename . " o " );
		$query->addJoin("," . Organization::_tablename . " org ");
		$query->addJoin("," . Inventory::_tablename .  " i ");
		$query->addJoin("," . Tank::_tablename . " t ");
		$query->addWhere(" r.reference_id = ord.po_id ");
		$query->addWhere(" r.org_organism_id = oo.org_organism_id ");
		$query->addWhere(" oo.organism_id = o.organism_id ");
		$query->addWhere(" r.supplier_org_id = org.org_id ");
		$query->addWhere(" r.inventory_id = i.inventory_id ");
		$query->addWhere(" i.tank_id = t.tank_id ");
		$query->addWhere(" r.doa_quantity > 0 ");
		$query->addWhere(" r.org_id = ". $orgId );

		// supplier
		if(strlen($supplierId) > 0) {
			$query->addWhere("r.supplier_org_id = " . $supplierId);
		}
		// tank
		if(strlen($tank) > 0) {
			$query->addWhere(" i.tank_id = " . $tank);	
		}
		// moid
		if(strlen($moid) > 0) {
			$query->addWhere("o.moid = '" . $moid . "'");	
		}
		// dates from - to
		if(strlen($dateFrom) > 0 && strlen($dateTo) > 0) {
			$query->addWhere(" r.date_receipt between least('" . $dateFrom . "','" . $dateTo . "') and greatest('" . $dateFrom . "','" . $dateTo . "')" );	
		}
		elseif(strlen($dateFrom) > 0) {
			$query->addWhere(" r.date_receipt >= '" . $dateFrom . "'" );
		}
		elseif(strlen($dateTo) > 0) {
			$query->addWhere(" r.date_receipt <= '" . $dateTo . "'");
		}
		// search PO
		if(strlen($searchPO) > 0) {
			$query->addWhere("ord.po_no regexp '" . $searchPO . "'");	
		}
			
		
		$db = DBCon::instance();
		// echo  $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		return $result;
				
	}
	
	public function getReceiptSupplierByOrgId($orgId)
	{
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("r.supplier_org_id, org.name supplier_name");
		$query->addFrom($this->_tableName . " r ");
		$query->addJoin("," . Organization::_tablename . " org ");
		$query->addWhere(" r.supplier_org_id = org.org_id ");
		$query->addWhere(" r.org_id = " . $orgId);
		$query->addGroupBy("r.supplier_org_id, org.name");
		
		$db = DBCon::instance();
		// echo  $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		return $result;
	}

	public function getMonthlyListOfDOASummary($orgType, $country="", $province="", $orgId=0, $dateFrom="", $dateTo="") {

		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("OrganizationAddress");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("QueryCreator");

		$query = new QueryCreator();
		$query->addSelect("date_format(r.date_receipt,'%Y%m') yearmonth, cast(100*(sum(r.doa_quantity)/sum(r.received_quantity)) as unsigned) doa_percentage");
		$query->addFrom($this->_tableName . " r ");
		$query->addJoin(", " . Organization::_tablename . " org ");
		$query->addJoin(" left join " . OrganizationAddress::_tablename . " oa on org.primary_address_id = oa.org_address_id and org.org_id = oa.org_id");
		$query->addJoin(" left join " . Address::_tablename . " a on oa.address_id = a.address_id");
		$query->addWhere("r.supplier_org_id = org.org_id");
		$query->addWhere("org.org_type = '" . $orgType . "'");
		$query->addGroupBy("date_format(r.date_receipt,'%Y%m')");

		// country
		if(strlen($country) > 0) {
			$query->addWhere("a.country_code = " . $country);
		}
		
		// province
		if(strlen($province) > 0) {
			$query->addWhere("a.province = " . $province);
		}
		
		// org 
		if(strlen($orgId) > 0 && $orgId > 0) {
			$query->addWhere("org.org_id = " . $orgId);
		}

		// dates from - to
		if(strlen($dateFrom) > 0 && strlen($dateTo) > 0) {
			$query->addWhere(" r.date_receipt between least('" . $dateFrom . "','" . $dateTo . "') and greatest('" . $dateFrom . "','" . $dateTo . "')" );	
		}
		elseif(strlen($dateFrom) > 0) {
			$query->addWhere(" r.date_receipt >= '" . $dateFrom . "'" );
		}
		elseif(strlen($dateTo) > 0) {
			$query->addWhere(" r.date_receipt <= '" . $dateTo . "'");
		}

		$db = DBCon::instance();
		// echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;

	}

 }
?>